package org.example.impl;

import org.example.DAO.ReviewDao;
import org.example.entity.Reply;
import org.example.entity.Review;

import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ReviewDaoImpl implements ReviewDao {
    private final DataSource dataSource;

    public ReviewDaoImpl(DataSource dataSource) {
        this.dataSource = dataSource;
    }


    @Override
    public List<Review> getReviewsByUser(int userId, int offset, int limit) throws SQLException {
        String sql = "SELECT * FROM reviews WHERE user_id = ? ORDER BY create_time DESC LIMIT ? OFFSET ?";
        List<Review> reviews = new ArrayList<>();
        try (Connection conn = dataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, userId);
            stmt.setInt(2, limit);
            stmt.setInt(3, offset);
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    reviews.add(mapRowToReview(rs));
                }
            }
        }
        return reviews;
    }

    @Override
    public int getReviewCountByUser(int userId) throws SQLException {
        String sql = "SELECT COUNT(*) FROM reviews WHERE user_id = ?";
        try (Connection conn = dataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, userId);
            try (ResultSet rs = stmt.executeQuery()) {
                return rs.next() ? rs.getInt(1) : 0;
            }
        }
    }
    @Override
    public Review getReviewById(int reviewId) throws SQLException {
        String sql = "SELECT * FROM reviews WHERE id = ?";
        try (Connection conn = dataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, reviewId);
            try (ResultSet rs = stmt.executeQuery()) {
                return rs.next() ? mapRowToReview(rs) : null;
            }
        }
    }


    @Override
    public int getReviewCountByContent(int contentId, String contentType) throws SQLException {
        String sql = "SELECT COUNT(*) FROM reviews WHERE content_id = ? AND content_type = ?";
        try (Connection conn = dataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, contentId);
            stmt.setString(2, contentType);
            try (ResultSet rs = stmt.executeQuery()) {
                return rs.next() ? rs.getInt(1) : 0;
            }
        }
    }

    @Override
    public Review getReviewByUserAndContent(int userId, int contentId, String contentType) throws SQLException {
        String sql = "SELECT * FROM reviews WHERE user_id = ? AND content_id = ? AND content_type = ?";
        try (Connection conn = dataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, userId);
            stmt.setInt(2, contentId);
            stmt.setString(3, contentType);
            try (ResultSet rs = stmt.executeQuery()) {
                return rs.next() ? mapRowToReview(rs) : null;
            }
        }
    }

    @Override
    public boolean addReview(Review review) throws SQLException {
        String sql = "INSERT INTO reviews (content_id, content_type, user_id, username, rating, content, is_anonymous, ip_address) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        try (Connection conn = dataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            setReviewParameters(stmt, review);
            return stmt.executeUpdate() > 0;
        }
    }

    @Override
    public boolean updateReview(Review review) throws SQLException {
        String sql = "UPDATE reviews SET rating = ?, content = ?, update_time = CURRENT_TIMESTAMP " +
                "WHERE id = ?";
        try (Connection conn = dataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, review.getRating());
            stmt.setString(2, review.getContent());
            stmt.setInt(3, review.getId());
            return stmt.executeUpdate() > 0;
        }
    }

    @Override
    public List<Review> getReviewsByContent(int contentId, String contentType) throws SQLException {
        String sql = "SELECT * FROM reviews WHERE content_id = ? AND content_type = ? ORDER BY create_time DESC";
        List<Review> reviews = new ArrayList<>();
        try (Connection conn = dataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, contentId);
            stmt.setString(2, contentType);
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    reviews.add(mapRowToReview(rs));
                }
            }
        }
        return reviews;
    }
    @Override
    public boolean addReply(Reply reply) throws SQLException {
        String sql = "INSERT INTO replies (review_id, user_id, username, content, create_time) VALUES (?, ?, ?, ?, ?)";
        try (Connection conn = dataSource.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, reply.getReviewId());
            pstmt.setInt(2, reply.getUserId());
            pstmt.setString(3, reply.getUsername());
            pstmt.setString(4, reply.getContent());
            pstmt.setTimestamp(5, reply.getCreateTime());
            return pstmt.executeUpdate() > 0;
        }
    }

    @Override
    public List<Reply> getRepliesByReview(int reviewId) throws SQLException {
        String sql = "SELECT * FROM replies WHERE review_id = ? ORDER BY create_time ASC";
        List<Reply> replies = new ArrayList<>();
        try (Connection conn = dataSource.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, reviewId);
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    Reply reply = new Reply();
                    reply.setId(rs.getInt("id"));
                    reply.setReviewId(rs.getInt("review_id"));
                    reply.setUserId(rs.getInt("user_id"));
                    reply.setUsername(rs.getString("username"));
                    reply.setContent(rs.getString("content"));
                    reply.setCreateTime(rs.getTimestamp("create_time"));
                    replies.add(reply);
                }
            }
        }
        return replies;
    }
    @Override
    public boolean deleteReview(int reviewId) throws SQLException {
        String sql = "DELETE FROM reviews WHERE id = ?";
        try (Connection conn = dataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, reviewId);
            return stmt.executeUpdate() > 0;
        }
    }

    private Review mapRowToReview(ResultSet rs) throws SQLException {
        Review review = new Review();
        review.setId(rs.getInt("id"));
        review.setContentId(rs.getInt("content_id"));
        review.setContentType(rs.getString("content_type"));
        review.setUserId(rs.getInt("user_id"));
        review.setUsername(rs.getString("username"));
        review.setRating(rs.getInt("rating"));
        review.setContent(rs.getString("content"));
        review.setIsAnonymous(rs.getBoolean("is_anonymous"));
        review.setIpAddress(rs.getString("ip_address"));
        review.setCreateTime(rs.getTimestamp("create_time"));
        review.setUpdateTime(rs.getTimestamp("update_time"));
        return review;
    }


    private void setReviewParameters(PreparedStatement stmt, Review review) throws SQLException {
        stmt.setInt(1, review.getContentId());
        stmt.setString(2, review.getContentType());
        stmt.setInt(3, review.getUserId());
        stmt.setString(4, review.getUsername());
        stmt.setInt(5, review.getRating());
        stmt.setString(6, review.getContent());
        stmt.setBoolean(7, review.getIsAnonymous());
        stmt.setString(8, review.getIpAddress());
    }
}